package com.summer_administration.mapper;

import com.summer_administration.pojo.*;
import com.summer_administration.pojo.Expenses;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ExpensesMapper {
    @Insert("INSERT INTO expenses(title,group_id,group_name, number, method, picture, date, state, remark, create_user, create_time, update_time) " +
            "VALUES(#{title},#{groupId},#{groupName}, #{number}, #{method}, #{picture}, #{date}, #{state}, #{remark}, #{createUser}, #{createTime}, #{updateTime})")
    void add(Expenses expense);

    List<Expenses> list(Integer userId);

    @Update("UPDATE expenses SET title=#{title}, group_name=#{groupName}, number=#{number}, method=#{method}, " +
            "picture=#{picture}, date=#{date}, state=#{state}, remark=#{remark}, update_time=#{updateTime} WHERE id=#{id}")
    void update(Expenses expenses);

    @Delete("DELETE FROM expenses WHERE id=#{id}")
    void delete(Integer id);

    @Select("SELECT g.group_name AS groupName, COALESCE(SUM(e.number), 0) AS totalAmount " +
            "FROM groupe g LEFT JOIN expenses e ON g.group_id = e.group_id " +
            "GROUP BY g.group_id, g.group_name " +
            "ORDER BY g.group_id")
    List<GroupExpenseId> selectGroupExpenses();

    @Select("SELECT e.method AS method, " +
            "COALESCE(SUM(e.number), 0) AS totalAmount " +
            "FROM expenses e " +
            "GROUP BY e.method " +       // 按 method 分组
            "ORDER BY e.method")         // 按 method 排序（可选）
    List<GroupExpenseDTO> selectByMethod();

    @Select("SELECT " +
            "s.state AS state, " +
            "COALESCE(SUM(e.number), 0) AS totalAmount " +
            "FROM (SELECT '是' AS state UNION SELECT '否') s " +  // 强制生成两种状态
            "LEFT JOIN expenses e ON s.state = e.state " +       // 左连接实际数据
            "GROUP BY s.state " +                               // 按状态分组
            "ORDER BY FIELD(s.state, '否', '是')")              // 保持原有排序
    List<StateExpenseDTO> selectByState();
}
